import sqlite3
conn = sqlite3.connect("CW1.db")

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d


def select_goods(name, model, process):
    conn = sqlite3.connect("CW1.db")
    conn.row_factory = dict_factory
    cursor = conn.cursor()
    sql = 'select * from goods where name like \'%' + name + '%\' and model like \'%' + model + '%\' and process like\'%' + process + '%\';'
    cursor.execute(sql)
    goods = cursor.fetchall()
    cursor.close()
    conn.commit()
    conn.close()
    return goods


def select_records():
    conn = sqlite3.connect('CW1.db')
    conn.row_factory = dict_factory
    cursor = conn.cursor()

    cursor.execute(
        'select time,name,model,factory,process,inorout,change,people from records left join goods on records.id=goods.id order by time desc ;')
    records = cursor.fetchall()
    cursor.close()
    conn.commit()
    conn.close()
    return records


def insert_goods(name, model, factory, process, price):
    conn = sqlite3.connect('CW1.db')
    conn.row_factory = dict_factory
    cursor = conn.cursor()

    cursor.execute('insert into goods values(null,?,?,?,?,?,0,0);', (name, model, factory, process, price,))
    cursor.close()
    conn.commit()
    conn.close()


def del_goods(id):
    conn = sqlite3.connect('CW1.db')
    conn.row_factory = dict_factory
    cursor = conn.cursor()

    cursor.execute('delete from goods where id=?;', (id,))
    cursor.close()
    conn.commit()
    conn.close()


def insert_records(id, kind, change, people):
    conn = sqlite3.connect('CW1.db')
    conn.row_factory = dict_factory
    cursor = conn.cursor()

    if kind == 1:
        cursor.execute('insert into records values(?,datetime(\'now\',\'localtime\'),?,?,?);',
                       (id, '入库', change, people,))
        cursor.execute('update goods set number=number+? where id=?;', (change, id,))
    else:
        cursor.execute('insert into records values(?,datetime(\'now\',\'localtime\'),?,?,?);',
                       (id, '出库', change, people,))
        cursor.execute('update goods set number=number-? where id=?;', (change, id,))
    cursor.close()
    conn.commit()
    conn.close()


def update_goods(id, name, model, process, factory, price,safenumber):
    conn = sqlite3.connect('CW1.db')
    conn.row_factory = dict_factory
    cursor = conn.cursor()

    cursor.execute('update goods set name=? ,model=?,process=?,factory=?,price=?,safenumber = ? where id=?;',
                   (name, model, process, factory, price,safenumber, id,))
    cursor.close()
    conn.commit()
    conn.close()


def count_goods(inorout, date1, date2):
    conn = sqlite3.connect('CW1.db')
    conn.row_factory = dict_factory
    cursor = conn.cursor()

    cursor.execute(
        'select goods.id as id,name,model,process,factory,price,number,inorout,sum(change) as sum_change,price*sum(change) as money from goods left join records on goods.id = records.id where inorout=? and time >=? and time<=? group by inorout,goods.id order by goods.id;',
        (inorout, date1, date2), )
    counts = cursor.fetchall()
    cursor.close()
    conn.commit()
    conn.close()
    return counts


def get_names(kind):
    conn = sqlite3.connect('CW1.db')
    conn.row_factory = dict_factory
    cursor = conn.cursor()
    cursor.execute('select {0} from names;'.format(str(kind)),)
    names = cursor.fetchall()
    cursor.close()
    conn.commit()
    conn.close()
    # print(names)
    return names

# def create_notification_table():
#     conn = sqlite3.connect('CW1.db')
#     conn.row_factory = dict_factory
#     cursor = conn.cursor()
#     cursor.execute('''
#         CREATE TABLE IF NOT EXISTS notifications (
#             id INTEGER PRIMARY KEY AUTOINCREMENT,
#             message TEXT NOT NULL,
#             is_read INTEGER DEFAULT 0,
#             created_at DATETIME DEFAULT CURRENT_TIMESTAMP
#         )
#     ''')
#     cursor.close()
#     conn.commit()
#     conn.close()


def insert_notification(message):
    conn = sqlite3.connect('CW1.db')
    conn.row_factory = dict_factory
    cursor = conn.cursor()
    cursor.execute('INSERT INTO notifications (message) VALUES (?)', (message,))
    cursor.close()
    conn.commit()
    conn.close()


def get_unread_notifications():
    conn = sqlite3.connect('CW1.db')
    conn.row_factory = dict_factory
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM notifications WHERE is_read = 0 ORDER BY created_at DESC')
    notifications = cursor.fetchall()
    cursor.close()
    conn.commit()
    conn.close()
    return notifications


def mark_notification_as_read(notification_id):
    conn = sqlite3.connect('CW1.db')
    conn.row_factory = dict_factory
    cursor = conn.cursor()
    cursor.execute('UPDATE notifications SET is_read = 1 WHERE id = ?', (notification_id,))
    cursor.close()
    conn.commit()
    conn.close()


def get_all_notifications():
    conn = sqlite3.connect('CW1.db')
    conn.row_factory = dict_factory
    cursor = conn.cursor()
    # 先查询未读通知，再查询已读通知
    cursor.execute('SELECT * FROM notifications ORDER BY is_read ASC, created_at DESC')
    notifications = cursor.fetchall()
    cursor.close()
    conn.commit()
    conn.close()
    return notifications